from typing import List

from pymysql import MySQLError, err
from pymysql import connect as sql_connect
from pymysql.connections import Connection as SqlConnection
from pymysql.cursors import Cursor as SqlCursor

from tqdm import tqdm

from bean.FieldInfo import FieldInfo
from bean.TableInfo import TableInfo
from utils.StringUtils import table_name_to_bean_name, field_name_to_property_name
from utils.CustomException import TableNotExist
from utils.TypeConvertion import sql_type_convert_to_java_type, SqlTypesEnum


class DatabaseUtils:
    __connection: SqlConnection = None
    __cursor: SqlCursor = None

    __SQL_SHOW_STATUS: str = "show table status"
    __SQL_SHOW_FULL_FIELDS: str = "show full fields from "
    __SQL_SHOW_INDEX: str = "show index from "

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close_connection()

    def __init__(self, host: str, port: int, user: str, password: str, database: str):

        try:
            self.__connection = sql_connect(host=host, port=port, user=user, password=password, database=database)
            self.__cursor = self.__connection.cursor()
        except MySQLError:
            self.close_connection()
            raise MySQLError(
                f"数据库链接失败\nhost:{host}\nport:{port}\nuser:{user}\npassword:{password}\ndatabase:{database}")
        except RuntimeError:
            raise MySQLError(f"数据库密码错误原密码为:{password}")
        except err.OperationalError:
            raise MySQLError("未启动mysql服务")

    def get_table_infos(self) -> List[TableInfo]:
        """
        获取表信息\n
        :return:表信息对象
        """
        self.__cursor.execute(self.__SQL_SHOW_STATUS)
        column_name_list = [data[0] for data in self.__cursor.description]
        data = self.__cursor.fetchall()
        result: List[TableInfo] = []

        for i in data:
            table_info = TableInfo()

            table_info.table_name = i[column_name_list.index("Name")]

            table_info.comment = i[column_name_list.index("Comment")]

            table_info.bean_name = table_name_to_bean_name(table_info.table_name, True, False)
            table_info.bean_param_name = table_name_to_bean_name(table_info.table_name, True)

            self.fill_table_info_field_map(table_info)
            self.fill_table_info_index_map(table_info)
            result.append(table_info)
        return result

    def fill_table_info_field_map(self, table_info: TableInfo) -> None:
        """
        填充table_info的filed_map字段
        :param table_info: 表的信息对象
        """

        try:
            self.__cursor.execute(self.__SQL_SHOW_FULL_FIELDS + table_info.table_name)
            column_name_list = [data[0] for data in self.__cursor.description]
        except MySQLError:
            raise TableNotExist(f"{table_info.table_name}表不在数据库中")
        data = self.__cursor.fetchall()

        for i in data:
            field_info = FieldInfo()
            """
            根据不同的列获取信息到给定的字段中
            """
            field_info.filed_name = i[column_name_list.index("Field")]
            field_info.comment = i[column_name_list.index("Comment")]
            if i[column_name_list.index("Extra")] == "auto_increment":
                field_info.is_auto_increment = True
                table_info.increment_lst.append(field_info)
            else:
                field_info.is_auto_increment = False
            sql_type = i[column_name_list.index("Type")]

            """
            sql类型中有例如varchar(10)这种形式的，下面是去除括号转换为varchar
            """

            if "(" in sql_type:
                sql_type = sql_type[0:sql_type.find("(")]
                field_info.sql_type = sql_type
            else:
                field_info.sql_type = sql_type

            """
            根据字段类型设置表中是否有特殊类型
            """

            if field_info.sql_type in SqlTypesEnum.date.value:
                table_info.have_date = True
            elif field_info.sql_type in SqlTypesEnum.date_time.value:
                table_info.have_date_time = True
            elif field_info.sql_type in SqlTypesEnum.decimal.value:
                table_info.have_big_decimal = True

            """
            设置java类型
            """
            field_info.java_type = sql_type_convert_to_java_type(field_info.sql_type)

            field_info.property_name = field_name_to_property_name(field_info.filed_name)

            table_info.field_map[field_info.filed_name] = field_info

    def fill_table_info_index_map(self, table_info: TableInfo) -> None:
        """
        获取字段索引
        :return:
        """

        try:
            self.__cursor.execute(self.__SQL_SHOW_INDEX + table_info.table_name)
            column_name_list = [data[0] for data in self.__cursor.description]
        except MySQLError:
            raise TableNotExist(f"{table_info.table_name}表不在数据库中")
        data = self.__cursor.fetchall()

        for i in data:
            if i[column_name_list.index("Non_unique")] == 0:
                filed_name = i[column_name_list.index("Column_name")]
                key_name = i[column_name_list.index("Key_name")]
                filed_info = table_info.field_map[filed_name]
                """
                将索引的字段加入
                """
                if key_name not in table_info.key_index_map.keys():
                    table_info.key_index_map[key_name] = [filed_info]
                else:
                    table_info.key_index_map[key_name].append(filed_info)

    def close_connection(self):
        """
        关闭数据库连接
        :return:
        """

        if self.__cursor is not None:
            self.__cursor.close()
        if self.__connection is not None:
            self.__connection.close()


if __name__ == "__main__":

    sqlUtils = DatabaseUtils(
        host="localhost",
        port=3306,
        user="root",
        password="123456",
        database="easylive"
    )
    f = sqlUtils.get_table_infos()

    """
    查看结果
    """
    with tqdm(total=len(f), desc="查看字段") as tq:
        for i in f:
            print("\n-----table-----\n")
            print(f"table_name : {i.table_name}")
            print(f"bean_name : {i.bean_name}")
            print(f"bean_param_name : {i.bean_param_name}")
            print(f"have_date : {i.have_date}")
            print(f"have_date_time : {i.have_date_time}")
            print(f"have_big_decimal : {i.have_big_decimal}")
            print(f"comment : {i.comment}")
            print(f"increment : {i.increment_lst}")
            print("\n----filed-----\n")
            for j in i.field_map.values():
                print()
                print(f"filed_name : {j.filed_name}")
                print(f"property_name : {j.property_name}")
                print(f"sql_type : {j.sql_type}")
                print(f"java_type : {j.java_type}")
                print(f"is_auto_increment : {j.is_auto_increment}")
                print(f"comment : {j.comment}")
                print()
            print("\n----index-----\n")
            for k in i.key_index_map:
                print(f"{k}:")
                for z in i.key_index_map[k]:
                    print(f"\tfiled_name : {z.filed_name}")
                    print(f"\tproperty_name : {z.property_name}")
                    print(f"\tsql_type : {z.sql_type}")
                    print(f"\tjava_type : {z.java_type}")
                    print(f"\tis_auto_increment : {z.is_auto_increment}")
                    print(f"\tcomment : {z.comment}")
            tq.update(1)
